import pandas as pd
import re


def load_douyin_data():
    """加载抖音平台数据"""
    dou_files = [
        (r'G:\工作\每日\抖音1.xlsx', '抖音1'),
        (r'G:\工作\每日\抖音2.xlsx', '抖音2'),
        (r'G:\工作\每日\抖音3.xlsx', '抖音3'),
        (r'G:\工作\每日\抖音4.xlsx', '抖音4')
    ]

    dou_dfs = []
    for file_path, platform in dou_files:
        df = pd.read_excel(file_path, dtype={'商品ID': str, '达人昵称': str})
        df['平台'] = platform
        dou_dfs.append(df)

    return pd.concat(dou_dfs)


def filter_data(dou):
    """数据筛选和预处理"""
    # 重命名列
    dou.columns = ['商品ID', '商家编码', '商品数量', '订单应付金额', '订单状态', '订单提交时间',
                   '支付完成时间', '售后状态', '流量来源', '达人昵称', '流量类型', '流量渠道',
                   '流量体裁', '平台实际承担优惠金额', '达人实际承担优惠金额', '平台']

    # 计算金额
    dou['订单应付金额加优惠'] = dou['订单应付金额'] + dou['平台实际承担优惠金额'] + dou['达人实际承担优惠金额']
    dou['退款金额'] = dou['订单应付金额'] + dou['平台实际承担优惠金额'] + dou['达人实际承担优惠金额']

    # 排除自播间达人
    exclude_creators = [
        '宛禾米线官方旗舰店', '宛禾米线速食官方旗舰店', '宛禾食品旗舰店',
        '宛禾食品旗舰店直播间', '宛禾米线', '宛禾速食官方旗舰店',
        '宛禾速食旗舰店', '宛禾食品', '宛禾速食直播间',
        '宛禾食品旗舰店甄选号', '宛禾牛宅', '宛禾食品旗舰店自播间',
        '宛禾官方旗舰店', '宛禾速食'
    ]

    date_concat = dou[~dou['达人昵称'].isin(exclude_creators)]
    date_concat_1 = date_concat.copy()

    # 数据清洗
    date_concat['商家编码'] = date_concat['商家编码'].str.strip()
    date_concat_1['商家编码'] = date_concat_1['商家编码'].str.strip()
    date_concat.dropna(subset=['商家编码'], inplace=True)

    return date_concat, date_concat_1


def process_order_data(date_concat_1, date_concat):
    """处理订单数据"""
    # 定义状态常量
    valid_order_status = [
        '部分发货', '待发货', '待配货', '等待确认收货', '交易成功',
        '卖家已发货，等待买家确认', '已发货', '已发货，待签收',
        '已发货未签收', '已完成', '已支付', '已签收',
        '买家已付款，等待卖家发货', '等待出库'
    ]

    invalid_after_sales_status = [
        '退款成功', '退款完成', '已全额退款', '(锁定)等待确认收货',
        '(删除)等待出库', '(删除)等待确认收货', '售后完成'
    ]

    # 单量统计
    date_concat_dl = date_concat_1[
        (date_concat_1['订单状态'].isin(valid_order_status)) &
        (~date_concat_1['售后状态'].isin(invalid_after_sales_status)) &
        (date_concat_1['订单应付金额'] >= 0.01)
        ]

    # 按平台、达人、商家编码分组统计
    quantity_stats = date_concat_dl.groupby([
        date_concat_dl['平台'],
        date_concat_dl['达人昵称'],
        date_concat_dl['商家编码']
    ], as_index=False).agg(商品数量=('商品数量', 'sum'))

    order_count_stats = date_concat_dl.groupby([
        date_concat_dl['平台'],
        date_concat_dl['达人昵称'],
        date_concat_dl['商家编码']
    ], as_index=False).agg(商品单量=('商品数量', 'count'))

    order_count_stats.to_excel(r'G:\结果\结果_待发货(单量)_达人.xlsx')

    # 销售额统计
    date_concat_s = date_concat[
        (date_concat['订单状态'].isin(valid_order_status)) &
        (date_concat['订单应付金额'] >= 0.01) &
        (date_concat['商家编码'] != '抖音直播间赠品') &
        (~date_concat['售后状态'].isin(invalid_after_sales_status))
        ]

    # 分类汇总销售金额
    sales_stats = date_concat_s.groupby([
        date_concat_s['平台'],
        date_concat_s['达人昵称']
    ]).agg(订单应付金额=('订单应付金额加优惠', 'sum'), 商品数量=('商品数量', 'count'))

    # 退款统计
    refund_data = date_concat[
        date_concat['售后状态'].isin([
            '退款成功', '退款完成', '已全额退款', '(锁定)等待确认收货',
            '(删除)等待出库', '(删除)等待确认收货', '售后完成'
        ])
    ]

    refund_stats = refund_data['退款金额'].groupby([
        refund_data['平台'],
        refund_data['达人昵称']
    ]).sum()

    # 整合销售和退款数据
    sales_summary = pd.merge(sales_stats, refund_stats, on=['平台', '达人昵称'], how='left')
    sales_summary.columns = ['销售额', '销售单量', '退款金额']
    sales_summary.fillna(0, inplace=True)
    sales_summary['总销售额'] = sales_summary['销售额'] + sales_summary['退款金额']
    sales_summary.to_excel(r'G:\结果\线上销售额汇总_达人.xlsx')

    return order_count_stats


def classify_product(x):
    """产品分类函数"""
    if '+' in x:
        return '其他'
    elif '窝子面' in x:
        return '窝子面'
    elif '140g火鸡面' in x or '火鸡面' in x:
        return '桶装火鸡面'
    elif '436g' in x or '螺蛳粉' in x or '436螺蛳' in x:
        return '螺蛳粉土豆粉版'
    elif '粉面两掺' in x:
        return '粉面两掺'
    elif '133g袋装' in x or '133g' in x:
        return '袋装板面'
    elif '板面桶装' in x:
        return '桶装板面'
    elif '乐享杯' in x:
        return '乐享杯桶装'
    elif '家庭版袋装米线' in x or '家庭版' in x or '家庭麻酱米线' in x or '乐享版家庭' in x:
        return '家庭版麻酱米线'
    elif '欢享土豆粉' in x or '欢享版土豆粉' in x:
        return '欢享土豆粉'
    elif '轻享版' in x:
        return '轻享土豆粉'
    elif '乐享版刀削面' in x or '乐享刀削面' in x:
        return '乐享刀削面'
    elif '乐享版土豆粉' in x or '乐享土豆粉' in x or '286g' in x:
        return '乐享土豆粉'
    elif '荆芥土豆粉' in x:
        return '荆芥土豆粉'
    elif '荆芥麻酱米线' in x or '荆芥袋装米线' in x:
        return '荆芥米线'
    elif '肥汁米线桶装' in x or '肥汁桶装' in x:
        return '肥汁桶装'
    elif '麻辣米线' in x:
        return '麻辣米线'
    elif '117g肥汁' in x or '肥汁米线117g' in x:
        return '117g肥汁米线'
    elif '203g肥汁' in x or '肥汁米线203g' in x:
        return '203g肥汁米线'
    elif '线下肥汁' in x:
        return '线下肥汁米线'
    elif '80g土豆粉' in x or '土豆粉80g' in x:
        return '其他'
    elif '板面底料1桶' in x or '板面调料' in x:
        return '其他'
    elif x == '青春龙年礼盒':
        return '经典土豆粉'
    elif '土豆粉' in x or '士豆粉' in x:
        return '经典土豆粉'
    elif '豆芽板面' in x or '166g' in x:
        return '豆芽板面'
    elif '板面' in x or '146g' in x:
        return '桶装板面'
    elif '桶' in x:
        return '石磨桶装'
    elif '石磨' in x or '经典' in x or '麻酱' in x or '传统' in x or '青春' in x or '麻辣' in x or '来点尝一尝' in x or '01款' in x or '联名' in x:
        return '袋装米线'
    elif '刀削面面饼' in x:
        return '其他'
    elif '刀削面' in x:
        return '刀削面'
    elif '烩面' in x:
        return '炝锅烩面'
    else:
        return '其他'


def extract_quantity(x):
    """提取产品件数"""
    if x in ['土豆粉龙年礼盒', '青春龙年礼盒']:
        return 6
    elif x == '刀削面龙年礼盒':
        return 3
    elif '烩面龙年礼盒' in x:
        return 6
    elif '乐享版' in x or '轻享版' in x:
        try:
            return re.findall(r"(\d+)袋", x)[-1]
        except:
            return 0
    elif '土豆粉' in x or '士豆粉' in x or '粉面两掺' in x:
        try:
            return re.findall(r"(\d+)袋", x)[-1]
        except:
            return 0
    elif '桶' in x:
        try:
            return re.findall(r"(\d+)桶", x)[-1]
        except:
            return 0
    elif '石磨' in x or '经典' in x or '麻酱' in x or '传统' in x or '青春' in x or '麻辣' in x or '来点尝一尝' in x or '联名' in x or '荆芥' in x or '袋装米线' in x or '肥汁' in x:
        try:
            return re.findall(r"(\d+)袋", x)[-1]
        except:
            return 0
    elif '01款' in x:
        return 1
    elif '刀削面' in x or '烩面' in x or '板面' in x:
        try:
            return re.findall(r"(\d+)袋", x)[-1]
        except:
            return 0
    else:
        return 0


def process_potato_noodles_data(order_count_stats):
    """处理土豆粉出单数据"""
    # 读取匹配表
    pp = pd.read_excel(r'G:\工作\商家编码匹配产品.xlsx')

    # 处理每行多订单情况 - 分列
    main_products = []
    for merchant_code in order_count_stats['商家编码']:
        if '+' in merchant_code:
            main_products.append(merchant_code.split('+')[0])
        elif '，' in merchant_code:
            parts = merchant_code.split('，')
            if '赠' in parts[0]:
                main_products.append(parts[0].split('赠')[0])
            else:
                main_products.append(parts[0])
        elif '赠' in merchant_code:
            main_products.append(merchant_code.split('赠')[0])
        elif '＋' in merchant_code:
            main_products.append(merchant_code.split('＋')[0])
        else:
            main_products.append(merchant_code)

    order_count_stats['主产品'] = main_products
    order_count_stats = order_count_stats.reset_index(drop=True)

    # 处理土豆粉出单
    potato_data = order_count_stats.loc[:, ['达人昵称', '主产品', '商品单量']]
    result_hz = potato_data.groupby([potato_data['达人昵称'], potato_data['主产品']], as_index=False).sum()

    # 产品分类和件数提取
    result_hz['分类'] = result_hz['主产品'].map(classify_product)
    result_hz['件数'] = result_hz['主产品'].map(extract_quantity)
    result_hz['件数'] = result_hz['件数'].astype(int)

    result_hz.to_excel(r'G:\结果\土豆粉出单达人.xlsx')


def main():
    """主函数"""
    # 加载数据
    dou = load_douyin_data()

    # 数据筛选和预处理
    date_concat, date_concat_1 = filter_data(dou)

    # 处理订单数据
    order_count_stats = process_order_data(date_concat_1, date_concat)

    # 处理土豆粉出单数据
    process_potato_noodles_data(order_count_stats)


if __name__ == "__main__":
    main()
